use crate::entity::data_items::{ActiveModel as DataItemActiveModel, Entity as DataItems};
use crate::entity::users::Entity as Users;
use crate::error::AppError;
use crate::models::{DataItem, DataItemWithUser, NewDataItem};
use sea_orm::{
    ActiveModelTrait, ColumnTrait, Condition, DatabaseConnection, EntityTrait, PaginatorTrait,
    QueryFilter, QueryOrder, QuerySelect, Set,
};

pub struct DataService {
    pub db: DatabaseConnection,
}

impl DataService {
    pub fn new(db: DatabaseConnection) -> Self {
        Self { db }
    }

    // Basic implementations
    pub async fn get_data_item(
        &self,
        id: i64,
        _user_id: i64,
    ) -> Result<Option<DataItem>, AppError> {
        let item = DataItems::find_by_id(id).one(&self.db).await?;
        Ok(item.map(|model| DataItem {
            id: model.id,
            name: model.name,
            description: model.description,
            project: model.project,
            source: model.source,
            file_path: model.file_path,
            file_hash: model.file_hash,
            file_size: model.file_size,
            file_type: model.file_type,
            is_folder: model.is_folder,
            file_count: model.file_count,
            user_id: model.user_id,
            parent_id: model.parent_id,
            is_public: model.is_public,
            created_at: chrono::DateTime::from_naive_utc_and_offset(model.created_at, chrono::Utc),
            updated_at: chrono::DateTime::from_naive_utc_and_offset(model.updated_at, chrono::Utc),
            dvc_metadata_id: model.dvc_metadata_id,
        }))
    }

    pub async fn create_data_item(&self, item: NewDataItem) -> Result<DataItem, AppError> {
        let now = chrono::Utc::now();
        let new_item = DataItemActiveModel {
            name: Set(item.name),
            description: Set(item.description),
            project: Set(item.project),
            source: Set(item.source),
            file_path: Set(item.file_path),
            file_hash: Set(item.file_hash),
            file_size: Set(item.file_size),
            file_type: Set(item.file_type),
            is_folder: Set(item.is_folder),
            file_count: Set(item.file_count),
            user_id: Set(item.user_id),
            parent_id: Set(item.parent_id),
            is_public: Set(item.is_public),
            created_at: Set(now.naive_utc()),
            updated_at: Set(now.naive_utc()),
            ..Default::default()
        };

        let model = new_item.insert(&self.db).await?;
        Ok(DataItem {
            id: model.id,
            name: model.name,
            description: model.description,
            project: model.project,
            source: model.source,
            file_path: model.file_path,
            file_hash: model.file_hash,
            file_size: model.file_size,
            file_type: model.file_type,
            is_folder: model.is_folder,
            file_count: model.file_count,
            user_id: model.user_id,
            parent_id: model.parent_id,
            is_public: model.is_public,
            created_at: chrono::DateTime::from_naive_utc_and_offset(model.created_at, chrono::Utc),
            updated_at: chrono::DateTime::from_naive_utc_and_offset(model.updated_at, chrono::Utc),
            dvc_metadata_id: model.dvc_metadata_id,
        })
    }

    // List user's own data items with user email
    pub async fn list_data_items_with_user(
        &self,
        user_id: i64,
        page: Option<i64>,
        page_size: Option<i64>,
    ) -> Result<(Vec<DataItemWithUser>, i64), AppError> {
        let page = page.unwrap_or(1);
        let page_size = page_size.unwrap_or(20);
        let offset = (page - 1) * page_size;

        // Get total count
        let total = DataItems::find()
            .filter(
                Condition::all()
                    .add(crate::entity::data_items::Column::UserId.eq(user_id))
                    .add(
                        Condition::any()
                            .add(crate::entity::data_items::Column::Name.not_like("%.dvc"))
                            .add(crate::entity::data_items::Column::Source.eq("dvc-git-hook")),
                    ),
            )
            .count(&self.db)
            .await?;

        // Get paginated items with user email
        let items = DataItems::find()
            .find_also_related(Users)
            .filter(
                Condition::all()
                    .add(crate::entity::data_items::Column::UserId.eq(user_id))
                    .add(
                        Condition::any()
                            .add(crate::entity::data_items::Column::Name.not_like("%.dvc"))
                            .add(crate::entity::data_items::Column::Source.eq("dvc-git-hook")),
                    ),
            )
            .order_by_desc(crate::entity::data_items::Column::CreatedAt)
            .offset(offset as u64)
            .limit(page_size as u64)
            .all(&self.db)
            .await?
            .into_iter()
            .map(|(data_item, user)| DataItemWithUser {
                id: data_item.id,
                name: data_item.name,
                description: data_item.description,
                project: data_item.project,
                source: data_item.source,
                file_path: data_item.file_path,
                file_hash: data_item.file_hash,
                file_size: data_item.file_size,
                file_type: data_item.file_type,
                is_folder: data_item.is_folder,
                file_count: data_item.file_count,
                user_id: data_item.user_id,
                parent_id: data_item.parent_id,
                is_public: data_item.is_public,
                created_at: chrono::DateTime::from_naive_utc_and_offset(
                    data_item.created_at,
                    chrono::Utc,
                ),
                updated_at: chrono::DateTime::from_naive_utc_and_offset(
                    data_item.updated_at,
                    chrono::Utc,
                ),
                dvc_metadata_id: data_item.dvc_metadata_id,
                user_email: user
                    .map(|u| u.email)
                    .unwrap_or_else(|| "unknown".to_string()),
            })
            .collect();

        Ok((items, total as i64))
    }

    // List user's items + all public items with user emails
    pub async fn list_user_and_public_data_items_with_user(
        &self,
        user_id: i64,
        page: Option<i64>,
        page_size: Option<i64>,
    ) -> Result<(Vec<DataItemWithUser>, i64), AppError> {
        let page = page.unwrap_or(1);
        let page_size = page_size.unwrap_or(20);
        let offset = (page - 1) * page_size;

        // Get total count (user's items + public items)
        let total = DataItems::find()
            .filter(
                Condition::all()
                    .add(
                        Condition::any()
                            .add(crate::entity::data_items::Column::UserId.eq(user_id))
                            .add(crate::entity::data_items::Column::IsPublic.eq(true)),
                    )
                    .add(
                        Condition::any()
                            .add(crate::entity::data_items::Column::Name.not_like("%.dvc"))
                            .add(crate::entity::data_items::Column::Source.eq("dvc-git-hook")),
                    ),
            )
            .count(&self.db)
            .await?;

        // Get paginated items with user email (user's items + public items, avoiding duplicates)
        let items = DataItems::find()
            .find_also_related(Users)
            .filter(
                Condition::all()
                    .add(
                        Condition::any()
                            .add(crate::entity::data_items::Column::UserId.eq(user_id))
                            .add(crate::entity::data_items::Column::IsPublic.eq(true)),
                    )
                    .add(
                        Condition::any()
                            .add(crate::entity::data_items::Column::Name.not_like("%.dvc"))
                            .add(crate::entity::data_items::Column::Source.eq("dvc-git-hook")),
                    ),
            )
            .order_by_desc(crate::entity::data_items::Column::CreatedAt)
            .offset(offset as u64)
            .limit(page_size as u64)
            .all(&self.db)
            .await?
            .into_iter()
            .map(|(data_item, user)| DataItemWithUser {
                id: data_item.id,
                name: data_item.name,
                description: data_item.description,
                project: data_item.project,
                source: data_item.source,
                file_path: data_item.file_path,
                file_hash: data_item.file_hash,
                file_size: data_item.file_size,
                file_type: data_item.file_type,
                is_folder: data_item.is_folder,
                file_count: data_item.file_count,
                user_id: data_item.user_id,
                parent_id: data_item.parent_id,
                is_public: data_item.is_public,
                created_at: chrono::DateTime::from_naive_utc_and_offset(
                    data_item.created_at,
                    chrono::Utc,
                ),
                updated_at: chrono::DateTime::from_naive_utc_and_offset(
                    data_item.updated_at,
                    chrono::Utc,
                ),
                dvc_metadata_id: data_item.dvc_metadata_id,
                user_email: user
                    .map(|u| u.email)
                    .unwrap_or_else(|| "unknown".to_string()),
            })
            .collect();

        Ok((items, total as i64))
    }

    // List only public data items with user emails
    pub async fn list_public_data_items_with_user(
        &self,
        page: Option<i64>,
        page_size: Option<i64>,
    ) -> Result<(Vec<DataItemWithUser>, i64), AppError> {
        let page = page.unwrap_or(1);
        let page_size = page_size.unwrap_or(20);
        let offset = (page - 1) * page_size;

        // Get total count
        let total = DataItems::find()
            .filter(
                Condition::all()
                    .add(crate::entity::data_items::Column::IsPublic.eq(true))
                    .add(
                        Condition::any()
                            .add(crate::entity::data_items::Column::Name.not_like("%.dvc"))
                            .add(crate::entity::data_items::Column::Source.eq("dvc-git-hook")),
                    ),
            )
            .count(&self.db)
            .await?;

        // Get paginated items with user email
        let items = DataItems::find()
            .find_also_related(Users)
            .filter(
                Condition::all()
                    .add(crate::entity::data_items::Column::IsPublic.eq(true))
                    .add(
                        Condition::any()
                            .add(crate::entity::data_items::Column::Name.not_like("%.dvc"))
                            .add(crate::entity::data_items::Column::Source.eq("dvc-git-hook")),
                    ),
            )
            .order_by_desc(crate::entity::data_items::Column::CreatedAt)
            .offset(offset as u64)
            .limit(page_size as u64)
            .all(&self.db)
            .await?
            .into_iter()
            .map(|(data_item, user)| DataItemWithUser {
                id: data_item.id,
                name: data_item.name,
                description: data_item.description,
                project: data_item.project,
                source: data_item.source,
                file_path: data_item.file_path,
                file_hash: data_item.file_hash,
                file_size: data_item.file_size,
                file_type: data_item.file_type,
                is_folder: data_item.is_folder,
                file_count: data_item.file_count,
                user_id: data_item.user_id,
                parent_id: data_item.parent_id,
                is_public: data_item.is_public,
                created_at: chrono::DateTime::from_naive_utc_and_offset(
                    data_item.created_at,
                    chrono::Utc,
                ),
                updated_at: chrono::DateTime::from_naive_utc_and_offset(
                    data_item.updated_at,
                    chrono::Utc,
                ),
                dvc_metadata_id: data_item.dvc_metadata_id,
                user_email: user
                    .map(|u| u.email)
                    .unwrap_or_else(|| "unknown".to_string()),
            })
            .collect();

        Ok((items, total as i64))
    }

    pub async fn get_unified_data_item(
        &self,
        id: i64,
        user_id: i64,
    ) -> Result<crate::models::UnifiedDataItem, AppError> {
        use crate::entity::{data_items, dvc_metadata};
        use sea_orm::{ColumnTrait, EntityTrait};

        // Get the data item with user info
        let data_item_with_user = DataItems::find_by_id(id)
            .find_also_related(Users)
            .filter(
                Condition::all().add(data_items::Column::Id.eq(id)).add(
                    Condition::any()
                        .add(data_items::Column::UserId.eq(user_id))
                        .add(data_items::Column::IsPublic.eq(true)),
                ),
            )
            .one(&self.db)
            .await?
            .ok_or(AppError::NotFound("Data item not found".to_string()))?;

        let (data_item_model, user_model) = data_item_with_user;

        // Convert to DataItemWithUser
        let data_item = crate::models::DataItemWithUser {
            id: data_item_model.id,
            name: data_item_model.name,
            description: data_item_model.description,
            project: data_item_model.project,
            source: data_item_model.source,
            file_path: data_item_model.file_path,
            file_hash: data_item_model.file_hash,
            file_size: data_item_model.file_size,
            file_type: data_item_model.file_type,
            is_folder: data_item_model.is_folder,
            file_count: data_item_model.file_count,
            user_id: data_item_model.user_id,
            parent_id: data_item_model.parent_id,
            is_public: data_item_model.is_public,
            created_at: chrono::DateTime::from_naive_utc_and_offset(
                data_item_model.created_at,
                chrono::Utc,
            ),
            updated_at: chrono::DateTime::from_naive_utc_and_offset(
                data_item_model.updated_at,
                chrono::Utc,
            ),
            dvc_metadata_id: data_item_model.dvc_metadata_id,
            user_email: user_model
                .map(|u| u.email)
                .unwrap_or_else(|| "unknown".to_string()),
        };

        // Check if there's associated DVC metadata
        let dvc_metadata = if let Some(dvc_metadata_id) = data_item_model.dvc_metadata_id {
            // Get DVC metadata item with user info
            if let Some((dvc_model, dvc_user)) = dvc_metadata::Entity::find_by_id(dvc_metadata_id)
                .find_also_related(Users)
                .one(&self.db)
                .await?
            {
                // Convert DVC metadata to DataItemWithUser format for compatibility
                Some(crate::models::DataItemWithUser {
                    id: dvc_model.id,
                    name: dvc_model.dvc_filename,
                    description: Some(format!("DVC metadata for {}", dvc_model.data_filename)),
                    project: dvc_model.project,
                    source: dvc_model.source,
                    file_path: dvc_model.dvc_file_path.unwrap_or_else(|| "".to_string()),
                    file_hash: dvc_model.dvc_file_hash,
                    file_size: dvc_model.data_file_size,
                    file_type: Some("dvc".to_string()),
                    is_folder: false,
                    file_count: None,
                    user_id: dvc_model.user_id,
                    parent_id: None,
                    is_public: false, // DVC metadata is typically not public
                    created_at: chrono::DateTime::from_naive_utc_and_offset(
                        dvc_model.created_at,
                        chrono::Utc,
                    ),
                    updated_at: chrono::DateTime::from_naive_utc_and_offset(
                        dvc_model.updated_at,
                        chrono::Utc,
                    ),
                    dvc_metadata_id: None, // DVC metadata doesn't reference itself
                    user_email: dvc_user
                        .map(|u| u.email)
                        .unwrap_or_else(|| "unknown".to_string()),
                })
            } else {
                None
            }
        } else {
            None
        };

        // Determine actual file path
        let actual_file_path = if let Some(hash) = &data_item.file_hash {
            // Use DVC cache path for content-addressed storage
            format!("cache/{}/{}", &hash[0..2], &hash[2..])
        } else {
            // Use direct file path
            data_item.file_path.clone()
        };

        let has_dvc_metadata = dvc_metadata.is_some();
        Ok(crate::models::UnifiedDataItem {
            data_item,
            dvc_metadata,
            actual_file_path,
            has_dvc_metadata,
        })
    }

    pub async fn get_data_item_by_name(
        &self,
        _name: &str,
        _user_id: i64,
    ) -> Result<Option<DataItem>, AppError> {
        Ok(None)
    }

    pub async fn get_data_item_by_hash(&self, _hash: &str) -> Result<Option<DataItem>, AppError> {
        Ok(None)
    }

    pub async fn create_unified_data_item(
        &self,
        user_id: i64,
        name: &str,
        dvc_filename: &str,
        description: Option<String>,
        project: Option<String>,
        source: String,
        file_hash: Option<String>,
        file_size: Option<i64>,
        file_type: Option<String>,
        is_folder: bool,
        file_count: Option<i64>,
        parent_id: Option<i64>,
        is_public: bool,
    ) -> Result<(DataItem, Option<crate::models::DvcMetadata>), AppError> {
        // Create the data item first
        let new_data_item = NewDataItem {
            name: name.to_string(),
            description,
            project: project.clone(),
            source: source.clone(),
            file_path: name.to_string(), // Use original filename as file path
            file_hash: file_hash.clone(),
            file_size,
            file_type,
            is_folder,
            file_count,
            user_id,
            parent_id,
            is_public,
        };

        let data_item = self.create_data_item(new_data_item).await?;

        // Create DVC metadata
        let dvc_metadata_model =
            crate::services::dvc_metadata_seaorm::DvcMetadataService::new(self.db.clone())
                .create_dvc_metadata(user_id, dvc_filename)
                .await?;

        // Update the data item to reference the DVC metadata
        use crate::entity::data_items::ActiveModel as DataItemActiveModel;
        use sea_orm::{ActiveModelTrait, Set};

        let updated_item = DataItemActiveModel {
            id: Set(data_item.id),
            dvc_metadata_id: Set(Some(dvc_metadata_model.id)),
            ..Default::default()
        };
        let model = updated_item.update(&self.db).await?;

        // Convert DVC metadata model to the expected return type
        let dvc_metadata = crate::models::DvcMetadata {
            id: dvc_metadata_model.id,
            user_id: dvc_metadata_model.user_id,
            dvc_filename: dvc_metadata_model.dvc_filename,
            data_filename: dvc_metadata_model.data_filename,
            data_file_hash: dvc_metadata_model.data_file_hash,
            data_file_size: dvc_metadata_model.data_file_size,
            dvc_file_path: dvc_metadata_model.dvc_file_path,
            dvc_file_hash: dvc_metadata_model.dvc_file_hash,
            project: dvc_metadata_model.project,
            source: dvc_metadata_model.source,
            created_at: chrono::DateTime::from_naive_utc_and_offset(
                dvc_metadata_model.created_at,
                chrono::Utc,
            ),
            updated_at: chrono::DateTime::from_naive_utc_and_offset(
                dvc_metadata_model.updated_at,
                chrono::Utc,
            ),
        };

        // Return the updated data item and DVC metadata
        let updated_data_item = DataItem {
            id: model.id,
            name: model.name,
            description: model.description,
            project: model.project,
            source: model.source,
            file_path: model.file_path,
            file_hash: model.file_hash,
            file_size: model.file_size,
            file_type: model.file_type,
            is_folder: model.is_folder,
            file_count: model.file_count,
            user_id: model.user_id,
            parent_id: model.parent_id,
            is_public: model.is_public,
            created_at: chrono::DateTime::from_naive_utc_and_offset(model.created_at, chrono::Utc),
            updated_at: chrono::DateTime::from_naive_utc_and_offset(model.updated_at, chrono::Utc),
            dvc_metadata_id: model.dvc_metadata_id,
        };

        Ok((updated_data_item, Some(dvc_metadata)))
    }

    pub async fn update_file_path(&self, _id: i64, _new_path: &str) -> Result<bool, AppError> {
        Ok(true)
    }

    pub async fn delete_data_item(&self, id: i64, user_id: i64) -> Result<bool, AppError> {
        use sea_orm::EntityTrait;

        // 1. Get item and verify ownership
        let item = DataItems::find_by_id(id)
            .one(&self.db)
            .await?
            .ok_or(AppError::NotFound("Data item not found".to_string()))?;

        // 2. Check ownership
        if item.user_id != user_id {
            return Err(AppError::Forbidden("Access denied".to_string()));
        }

        // 3. Check if public (policy: public data cannot be deleted by users)
        if item.is_public {
            return Err(AppError::BadRequest(
                "Public data cannot be deleted. Contact an administrator for removal.".to_string(),
            ));
        }

        // 4. Delete from database (CASCADE will handle related records)
        let delete_result = DataItems::delete_by_id(id).exec(&self.db).await?;

        Ok(delete_result.rows_affected > 0)
    }

    pub async fn update_data_item(
        &self,
        id: i64,
        user_id: i64,
        name: Option<String>,
        description: Option<String>,
        project: Option<String>,
        _is_public: Option<bool>,
    ) -> Result<DataItem, AppError> {
        use sea_orm::{ActiveModelTrait, EntityTrait, Set};

        // 1. Get item and verify ownership
        let item = DataItems::find_by_id(id)
            .one(&self.db)
            .await?
            .ok_or(AppError::NotFound("Data item not found".to_string()))?;

        // 2. Check ownership
        if item.user_id != user_id {
            return Err(AppError::Forbidden("Access denied".to_string()));
        }

        // 3. Update fields
        let mut active_model: DataItemActiveModel = item.into();
        if let Some(name) = name {
            active_model.name = Set(name);
        }
        if let Some(description) = description {
            active_model.description = Set(Some(description));
        }
        if let Some(project) = project {
            active_model.project = Set(Some(project));
        }

        // 4. Save changes
        let updated_item = active_model.update(&self.db).await?;

        // 5. Convert back to DataItem
        Ok(DataItem {
            id: updated_item.id,
            name: updated_item.name,
            description: updated_item.description,
            project: updated_item.project,
            source: updated_item.source,
            file_path: updated_item.file_path,
            file_hash: updated_item.file_hash,
            file_size: updated_item.file_size,
            file_type: updated_item.file_type,
            is_folder: updated_item.is_folder,
            file_count: updated_item.file_count,
            user_id: updated_item.user_id,
            parent_id: updated_item.parent_id,
            is_public: updated_item.is_public,
            created_at: chrono::DateTime::from_naive_utc_and_offset(
                updated_item.created_at,
                chrono::Utc,
            ),
            updated_at: chrono::DateTime::from_naive_utc_and_offset(
                updated_item.updated_at,
                chrono::Utc,
            ),
            dvc_metadata_id: updated_item.dvc_metadata_id,
        })
    }

    pub async fn update_public_status(
        &self,
        id: i64,
        user_id: i64,
        is_public: bool,
    ) -> Result<(), AppError> {
        use sea_orm::{ActiveModelTrait, EntityTrait, Set};

        // 1. Get item and verify ownership
        let item = DataItems::find_by_id(id)
            .one(&self.db)
            .await?
            .ok_or(AppError::NotFound("Data item not found".to_string()))?;

        // 2. Check ownership
        if item.user_id != user_id {
            return Err(AppError::Forbidden("Access denied".to_string()));
        }

        // 3. Update public status
        let mut active_model: DataItemActiveModel = item.into();
        active_model.is_public = Set(is_public);
        active_model.updated_at = Set(chrono::Utc::now().naive_utc());

        // 4. Save changes
        active_model.update(&self.db).await?;

        Ok(())
    }

    // Admin methods
    pub async fn list_data_items(
        &self,
        _user_id: i64,
        _page: Option<i64>,
        _page_size: Option<i64>,
    ) -> Result<(Vec<DataItem>, i64), AppError> {
        Ok((vec![], 0))
    }

    pub async fn delete_user_data_items(&self, _user_id: i64) -> Result<i64, AppError> {
        Ok(0)
    }

    pub async fn get_data_item_admin(&self, id: i64) -> Result<DataItem, AppError> {
        use sea_orm::EntityTrait;

        // 1. Get item (admin can access any item)
        let item = DataItems::find_by_id(id)
            .one(&self.db)
            .await?
            .ok_or(AppError::NotFound("Data item not found".to_string()))?;

        // 2. Convert to DataItem
        Ok(DataItem {
            id: item.id,
            name: item.name,
            description: item.description,
            project: item.project,
            source: item.source,
            file_path: item.file_path,
            file_hash: item.file_hash,
            file_size: item.file_size,
            file_type: item.file_type,
            is_folder: item.is_folder,
            file_count: item.file_count,
            user_id: item.user_id,
            parent_id: item.parent_id,
            is_public: item.is_public,
            created_at: chrono::DateTime::from_naive_utc_and_offset(item.created_at, chrono::Utc),
            updated_at: chrono::DateTime::from_naive_utc_and_offset(item.updated_at, chrono::Utc),
            dvc_metadata_id: item.dvc_metadata_id,
        })
    }

    pub async fn delete_data_item_admin(&self, id: i64) -> Result<bool, AppError> {
        use sea_orm::EntityTrait;

        // 1. Get item (admin can delete any item)
        let _item = DataItems::find_by_id(id)
            .one(&self.db)
            .await?
            .ok_or(AppError::NotFound("Data item not found".to_string()))?;

        // 2. Delete from database (CASCADE will handle related records)
        let delete_result = DataItems::delete_by_id(id).exec(&self.db).await?;

        Ok(delete_result.rows_affected > 0)
    }

    pub async fn cleanup_orphaned_files(&self, _storage_path: &str) -> Result<i64, AppError> {
        Ok(0)
    }

    // Helper method to get user_id from email
    pub async fn get_user_id_by_email(&self, email: &str) -> Result<i64, AppError> {
        let user_model = Users::find()
            .filter(crate::entity::users::Column::Email.eq(email))
            .one(&self.db)
            .await?
            .ok_or_else(|| AppError::NotFound("User not found".to_string()))?;

        Ok(user_model.id)
    }

    // Admin data management methods
    pub async fn list_public_data_with_safety(
        &self,
    ) -> Result<Vec<crate::handlers::admin::PublicDataItem>, AppError> {
        use crate::entity::{data_items, data_lineage, users};
        use sea_orm::{ColumnTrait, EntityTrait, QueryOrder};

        let items = data_items::Entity::find()
            .filter(data_items::Column::IsPublic.eq(true))
            .order_by_desc(data_items::Column::CreatedAt)
            .all(&self.db)
            .await?;

        let mut result = Vec::new();
        let now = chrono::Utc::now();

        for item in items {
            // Count lineage relationships
            let lineage_count = data_lineage::Entity::find()
                .filter(
                    data_lineage::Column::ChildId
                        .eq(item.id)
                        .or(data_lineage::Column::ParentId.eq(item.id)),
                )
                .count(&self.db)
                .await?;

            // Calculate age in days
            let item_datetime: chrono::DateTime<chrono::Utc> =
                chrono::DateTime::from_naive_utc_and_offset(item.created_at, chrono::Utc);
            let age_days = (now.signed_duration_since(item_datetime).num_days()).max(0) as i64;

            // Calculate safety score
            let safety_score = self
                .calculate_safety_score(item.id, age_days, lineage_count as i64)
                .await?;

            // Get user email separately
            let user = users::Entity::find_by_id(item.user_id)
                .one(&self.db)
                .await?;

            result.push(crate::handlers::admin::PublicDataItem {
                id: item.id,
                name: item.name,
                description: item.description,
                project: item.project,
                source: item.source,
                file_size: item.file_size,
                user_id: item.user_id,
                user_email: user
                    .map(|u| u.email)
                    .unwrap_or_else(|| "Unknown".to_string()),
                is_public: item.is_public,
                created_at: chrono::DateTime::from_naive_utc_and_offset(
                    item.created_at,
                    chrono::Utc,
                ),
                updated_at: chrono::DateTime::from_naive_utc_and_offset(
                    item.updated_at,
                    chrono::Utc,
                ),
                lineage_count: lineage_count as i64,
                age_days,
                safety_score,
            });
        }

        Ok(result)
    }

    pub async fn analyze_data_safety(
        &self,
    ) -> Result<crate::handlers::admin::DataAnalysis, AppError> {
        use crate::entity::data_items;
        use sea_orm::{ColumnTrait, EntityTrait};

        // Total public data
        let total_public_data = data_items::Entity::find()
            .filter(data_items::Column::IsPublic.eq(true))
            .count(&self.db)
            .await?;

        // Total storage (simplified)
        let storage_result = data_items::Entity::find()
            .filter(data_items::Column::IsPublic.eq(true))
            .select_only()
            .column(data_items::Column::FileSize)
            .into_tuple::<Option<i64>>()
            .all(&self.db)
            .await?;

        let total_storage_mb: f64 =
            storage_result.iter().filter_map(|&size| size).sum::<i64>() as f64 / (1024.0 * 1024.0);

        // Orphaned data (users deleted) - simplified for now
        let orphaned_count = 0; // TODO: Implement proper orphaned detection

        // Duplicates (simplified - count items with same names)
        // For now, return 0 as this is a complex query
        let duplicate_count = 0;

        // Old unused data (>90 days, no lineage) - simplified for now
        let old_unused_count = 0; // TODO: Implement proper old unused detection

        // Safe to delete (orphaned + old unused)
        let safe_to_delete_count = orphaned_count + old_unused_count;

        Ok(crate::handlers::admin::DataAnalysis {
            total_public_data: total_public_data as i64,
            total_storage_mb,
            orphaned_count,
            duplicate_count,
            old_unused_count,
            safe_to_delete_count,
        })
    }

    pub async fn bulk_delete_safe_data(
        &self,
    ) -> Result<crate::handlers::admin::GarbageCollectResult, AppError> {
        use crate::entity::data_items;
        use sea_orm::EntityTrait;

        // Find safe to delete items (simplified - just return empty for now)
        let safe_items: Vec<data_items::Model> = Vec::new(); // TODO: Implement proper safe item detection

        let mut files_removed = 0;
        let mut space_freed = 0;

        for item in safe_items {
            // Delete from database
            let delete_result = data_items::Entity::delete_by_id(item.id)
                .exec(&self.db)
                .await?;

            if delete_result.rows_affected > 0 {
                files_removed += 1;
                if let Some(size) = item.file_size {
                    space_freed += size;
                }
            }
        }

        Ok(crate::handlers::admin::GarbageCollectResult {
            files_removed,
            space_freed,
        })
    }

    async fn calculate_safety_score(
        &self,
        _item_id: i64,
        age_days: i64,
        lineage_count: i64,
    ) -> Result<f64, AppError> {
        // Simple safety scoring algorithm
        let age_score = (age_days as f64 / 365.0).min(1.0); // Older = safer
        let usage_score = 1.0 - (lineage_count as f64 / 10.0).max(0.0); // Less used = safer
        let base_score = 0.3; // Base safety for all public data

        let safety_score = base_score + (age_score * 0.4) + (usage_score * 0.3);
        Ok(safety_score.min(1.0))
    }
}
